package com.example.studentgrades.dao;

import com.example.studentgrades.model.Score;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ScoreDAO {
    private Connection connection;

    // 构造方法
    public ScoreDAO(Connection connection) {
        this.connection = connection;
    }

    // 添加成绩
    public void addScore(Score score) throws SQLException {
        String sql = "INSERT INTO scores (student_id, subject, score) VALUES (?, ?, ?)";
        try (PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setInt(1, score.getStudentId());
            ps.setString(2, score.getSubject());
            ps.setInt(3, score.getScore());
            ps.executeUpdate();
        }
    }

    // 根据ID删除成绩
    public void deleteScore(int scoreId) throws SQLException {
        String sql = "DELETE FROM scores WHERE score_id = ?";
        try (PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setInt(1, scoreId);
            ps.executeUpdate();
        }
    }

    // 更新成绩信息
    public void updateScore(Score score) throws SQLException {
        String sql = "UPDATE scores SET student_id = ?, subject = ?, score = ? WHERE score_id = ?";
        try (PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setInt(1, score.getStudentId());
            ps.setString(2, score.getSubject());
            ps.setInt(3, score.getScore());
            ps.setInt(4, score.getScoreId());
            ps.executeUpdate();
        }
    }

    // 查询所有成绩
    public List<Score> getAllScores() throws SQLException {
        List<Score> scores = new ArrayList<>();
        String sql = "SELECT * FROM scores";
        try (PreparedStatement ps = connection.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                Score score = new Score();
                score.setScoreId(rs.getInt("score_id"));
                score.setStudentId(rs.getInt("student_id"));
                score.setSubject(rs.getString("subject"));
                score.setScore(rs.getInt("score"));
                scores.add(score);
            }
        }
        return scores;
    }

    // 根据ID查询成绩
    public List<Score> findScoreById(int student) throws SQLException {
        List<Score> scores = new ArrayList<>();
        String sql = "SELECT * FROM scores WHERE student_id = ?";
        try (PreparedStatement ps = connection.prepareStatement(sql);
             ) {
            ps.setInt(1,student);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Score score = new Score();
                score.setScoreId(rs.getInt("score_id"));
                score.setStudentId(rs.getInt("student_id"));
                score.setSubject(rs.getString("subject"));
                score.setScore(rs.getInt("score"));
                scores.add(score);
            }
        }
        return scores;
    }

    public Score findOneById(int student) throws SQLException {
        Score score = new Score();
        String sql = "SELECT * FROM scores WHERE score_id = ?";
        try (PreparedStatement ps = connection.prepareStatement(sql);) {
            ps.setInt(1,student);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                score.setScoreId(rs.getInt("score_id"));
                score.setStudentId(rs.getInt("student_id"));
                score.setSubject(rs.getString("subject"));
                score.setScore(rs.getInt("score"));
            }
        }
        return score;
    }
}

